package jdbc;

import utils.JdbcUtils;
import org.junit.Test;

import java.sql.*;

public class CURD {

    @Test
    public void create() throws SQLException {
        /*
        * 增加
        * */
        Connection conn = null;
        Statement st = null;
        String sql = "insert into user(username, birthday, sex, address) values('liyue', '1992-05-03', 'w', 'sichuan')";
        conn = JdbcUtils.getConnection();
        st = conn.createStatement();
        int num = st.executeUpdate(sql);
        System.out.println(num);
        JdbcUtils.free(st, conn, null);
    }

    @Test
    public void update() throws SQLException {
        /*
        * 更新
        * */
        Connection conn = null;
        Statement st = null;
        String sql = "UPDATE user SET sex='h' WHERE id=2";
        conn = JdbcUtils.getConnection();
        st = conn.createStatement();
        int num = st.executeUpdate(sql);
        System.out.println(num);
        JdbcUtils.free(st, conn, null);
    }

    @Test
    public void read() throws SQLException {
        /*
        * 查询
        * */
        Connection conn = null;
        Statement st = null;
        String sql = "SELECT username, birthday from user ";

        conn = JdbcUtils.getConnection();
        st = conn.createStatement();
        ResultSet rs= st.executeQuery(sql);

        while (rs.next()) {
            System.out.println(rs.getObject("username"));
            System.out.println(rs.getObject("birthday"));
        }
        JdbcUtils.free(st, conn, rs);
    }

    @Test
    public void preRead() throws SQLException{
        /*
        * preparedStatement查询
        * */
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "SELECT username, birthday from user where username=?";

        conn = JdbcUtils.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, "ljw");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.println(rs.getObject("birthday"));
        }
        JdbcUtils.free(ps, conn, rs);
    }

    @Test
    public void delete() throws SQLException{
        /*
        * 删除
        * */
        Connection conn = null;
        Statement st = null;
        String sql = "delete FROM user WHERE username='liyue'";

        conn = JdbcUtils.getConnection();
        st = conn.createStatement();
        int num = st.executeUpdate(sql);
        System.out.println(num);
        JdbcUtils.free(st, conn, null);
    }
}
